package user

import (
	"gpt-api/framework/ginplus"
	"gpt-api/framework/log"
	"gpt-api/pkg/model"
	"time"

	"github.com/gin-gonic/gin"
)

// AddMember 增加成员信息
func (h *Handler) AddMember(c *gin.Context) error {
	var req struct {
		MemberID         int       `json:"member_id"`
		FamilyID         int       `json:"family_id"`
		FirstName        string    `json:"first_name" binding:"required"`
		LastName         string    `json:"last_name" binding:"required"`
		Gender           string    `json:"gender" binding:"required"`
		BirthDate        time.Time `json:"birth_date"`
		DeathDate        time.Time `json:"death_date"`
		AvatarURL        string    `json:"avatar_url"`
		Generation       int       `json:"generation"`
		RelationshipType int       `json:"relationship_type"`
	}

	if err := c.Bind(&req); err != nil {
		return ginplus.ReplyBadRequest(c, err)
	}

	member := model.Member{}

	log.Info("Adding member", log.Var("member", member))
	err := h.db.Create(&member).Error
	if err != nil {
		return ginplus.ReplyInternalError(c, err)
	}
	//a := member.MemberID
	member_rela := model.Relations{}

	log.Info("Adding member_re", log.Var("member", member))
	errs := h.db.Create(&member_rela).Error
	if errs != nil {
		return ginplus.ReplyInternalError(c, errs)
	}
	result, errh := h.GetFamilyTrees(req.MemberID)

	if errh != nil {
		return ginplus.ReplyInternalError(c, errh)
	}

	return ginplus.ReplyJSON(c, result)
}

// FamilyTreeResult 是家族树查询结果的模型
type FamilyTreeResult struct {
	MemberID         int    `gorm:"column:member_id"`
	MemberFirstName  string `gorm:"column:member_first_name"`
	MemberLastName   string `gorm:"column:member_last_name"`
	RelationshipType string `gorm:"column:relationship_type"`
	RelationshipSide string `gorm:"column:relationship_side"`
	Level            int    `gorm:"column:level"`
	//Path                    []string `gorm:"column:path"`
	RelatedMemberID         int    `gorm:"column:related_member_id"`
	RelatedMemberFirstName  string `gorm:"column:related_member_first_name"`
	RelatedMemberLastName   string `gorm:"column:related_member_last_name"`
	RelatedMemberGeneration int    `gorm:"column:related_member_generation"`
}

// GetFamilyTree 查询家族树
func (h *Handler) GetFamilyTree(initialMemberID int) ([]FamilyTreeResult, error) {
	var results []FamilyTreeResult

	// 执行 WITH RECURSIVE 查询
	result := h.db.Raw(`
		WITH RECURSIVE family_tree AS (
		    SELECT
		        m.member_id,
		        m.first_name AS member_first_name,
		        m.last_name AS member_last_name,
		        m.generation,
		        1 AS level,
		        ARRAY[m.member_id] AS path,
		        NULL::VARCHAR AS relationship_type,
		        NULL::VARCHAR AS relationship_side
		    FROM
		        members m
		    WHERE
		        m.member_id = ?
		    
		    UNION ALL
		    
		    SELECT
		        mr.related_member_id AS member_id,
		        m.first_name AS member_first_name,
		        m.last_name AS member_last_name,
		        m.generation,
		        ft.level + 1 AS level,
		        path || mr.related_member_id,
		        r.relationship_type,
		        mr.relationship_side
		    FROM
		        family_tree ft
		    JOIN
		        member_relationships mr ON ft.member_id = mr.member_id
		    JOIN
		        members m ON mr.related_member_id = m.member_id
		    JOIN
		        relationships r ON mr.relationship_id = r.relationship_id
		    WHERE
		        ft.level < 10
		        AND NOT (mr.related_member_id = ANY(ft.path))
		)
		SELECT
		    ft.member_id,
		    ft.member_first_name,
		    ft.member_last_name,
		    ft.relationship_type,
		    ft.relationship_side,
		    ft.level,
		    ft.path,
		    m2.member_id AS related_member_id,
		    m2.first_name AS related_member_first_name,
		    m2.last_name AS related_member_last_name,
		    m2.generation AS related_member_generation
		FROM
		    family_tree ft
		JOIN
		    members m1 ON ft.member_id = m1.member_id
		LEFT JOIN
		    LATERAL (SELECT unnest(ft.path) AS member_id) p ON true
		LEFT JOIN
		    members m2 ON p.member_id = m2.member_id
		ORDER BY
		    ft.level, ft.member_id
	`, initialMemberID).Scan(&results)

	if result.Error != nil {
		return nil, result.Error
	}

	return results, nil
}

func (h *Handler) GetFamilyTrees(initialMemberID int) ([]FamilyTreeResult, error) {
	var results []FamilyTreeResult

	// 执行 WITH RECURSIVE 查询
	result := h.db.Raw(`
		WITH RECURSIVE family_tree AS (
    -- 初始化查询，从某一成员（如初始成员ID为2）开始
    SELECT
        m.member_id,
        m.first_name,
        m.last_name,
        m.generation,
        1 AS level,
        ARRAY[m.member_id] AS path,
        NULL::VARCHAR AS relationship_type, -- 初始查询没有关系类型
        NULL::VARCHAR AS relationship_side -- 初始查询没有关系来源
    FROM
        members m
    WHERE
        m.member_id = ? -- 替换为实际的初始成员ID

    UNION ALL

    -- 递归查找所有代际关系，限制最多查10代，并避免重复
    SELECT
        mr.related_member_id AS member_id,
        m.first_name,
        m.last_name,
        m.generation,
        ft.level + 1 AS level,
        path || mr.related_member_id,
        r.relationship_type,
        mr.relationship_side
    FROM
        family_tree ft
    JOIN
        member_relationships mr ON ft.member_id = mr.member_id
    JOIN
        members m ON mr.related_member_id = m.member_id
    JOIN
        relationships r ON mr.relationship_id = r.relationship_id
    WHERE
        ft.level < 10 -- 限制最多查10代
        AND NOT (mr.related_member_id = ANY(ft.path)) -- 避免重复成员
)
SELECT
    ft.member_id,
    m1.first_name AS member_first_name,
    m1.last_name AS member_last_name,
    ft.relationship_type,
    ft.relationship_side,
    ft.level,
    ft.path,
    m2.member_id AS related_member_id,
    m2.first_name AS related_member_first_name,
    m2.last_name AS related_member_last_name,
    m2.generation AS related_member_generation
FROM
    family_tree ft
JOIN
    members m1 ON ft.member_id = m1.member_id
LEFT JOIN
    LATERAL (SELECT unnest(ft.path) AS member_id) p ON p.member_id = m1.member_id
LEFT JOIN
    members m2 ON p.member_id = m2.member_id
ORDER BY
    ft.generation, ft.level, ft.member_id;`, initialMemberID).Scan(&results)

	if result.Error != nil {
		return nil, result.Error
	}

	return results, nil
}
